import pymysql
import pandas as pd
from datetime import datetime
from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import time
from sqlalchemy import create_engine
from db_conn.dbConn import conn_db_engine
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('ryerp_local')
# 定义早上8点和晚上8点的时间
current_time = datetime.now()
morning_time = current_time.replace(hour=8, minute=0, second=0, microsecond=0)
evening_time = current_time.replace(hour=20, minute=0, second=0, microsecond=0)
yesterday_evening_time = evening_time - pd.DateOffset(days=1)
# 检查当前时间是在早上8点和晚上8点之间，还是在晚上8点之后
if current_time >= evening_time:
    # 如果当前时间在晚上8点之后，那么前一个时间点是晚上8点
    previous_time_point = evening_time
elif current_time >= morning_time:
    # 如果当前时间在早上8点和晚上8点之间，那么前一个时间点是早上8点
    previous_time_point = morning_time
elif current_time < morning_time:
    # 如果当前时间在早上8点之前，那么前一个时间点是昨天晚上8点
    previous_time_point = yesterday_evening_time
sql1 = 'SELECT * FROM iotdemo WHERE receive_time >= %s'
df = pd.read_sql(sql1,con=conn1,params=(previous_time_point,))

df_sock_time = df[df['type']>'0']

def filter_consecutive_type_1(group):
    # 创建一个新列，表示上一行的type
    group['prev_type'] = group['type'].shift(1)
    group['prev_num'] = group['num'].shift(1)
    group['prev_receive_time'] = group['receive_time'].shift(1)
    # 筛选出连续两行type都为1的行
    return group[(group['type'] == '1') & (group['prev_type'] == '1')]

# 应用上述函数
df_sock_time = df_sock_time.groupby('dev').apply(filter_consecutive_type_1).drop(columns=['prev_type'])
df_sock_time = df_sock_time.reset_index(drop=True)
df_sock_time['avg_time'] = df_sock_time.apply(lambda row: (row['receive_time'] - row['prev_receive_time']).total_seconds() / (row['num'] - row['prev_num']) if row['num']!=row['prev_num'] else 0, axis=1)
df_sock_time['avg_time'] = df_sock_time['avg_time'].apply(lambda x: x if x <150 and x>30 else -1.0)
df_sock_time = df_sock_time[df_sock_time['avg_time']!=-1.0]
df_sock_time = df_sock_time[['dev','avg_time']].groupby('dev').mean().reset_index()


print(df_sock_time)


df = pd.read_sql("SELECT * FROM mm_new_weave_production_machine", conn2)
df['production'] = df['production'].apply(lambda x: x // 2 + 1)
sql2 = 'DELETE FROM  mm_new_weave_production_machine'
cursor = connect.cursor()
cursor.execute(sql2)
connect.commit()
df.to_sql('mm_new_weave_production_machine', conn2 , if_exists='append', index=False)

df = pd.read_sql("SELECT * FROM wms_outbounds_register_matirial", conn2)
print(df.columns)





